﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using dotNails.CodeGen.Models;
using Microsoft.SqlServer.Management.Smo;
using System.Data;
using System.Text.RegularExpressions;


namespace dotNails.CodeGen.Util {
	public class DbmlUtil {

		public static void GetSchemaAndNameFromFullName(string fullName, out string schema, out string name) {
			schema = fullName.Substring(0, fullName.IndexOf("."));
			// Strip brackets out of schema
			schema = schema.Replace("[", "");
			schema = schema.Replace("]", "");

			name = fullName.Substring(schema.Length + 1);
			// Strip brackets out of name
			name = name.Replace("[", "");
			name = name.Replace("]", "");
		}

		public static string GetTableNameFromDbmlTableName(string schemaName, string dbmlTableName) {
			string tableName = dbmlTableName.Substring(0, dbmlTableName.IndexOf("."));
			return tableName;
		}

		public static string GetAssociationName(TableRelationship relationship) {
			return relationship.ReferencedTable + "_" + relationship.ReferencingTable;
		}

		public static string CreateAssociationName(TableRelationship relationship, List<Association> existingAssociations, ref bool isDupe) {
			string name = relationship.ReferencedTable + "_" + relationship.ReferencingTable;

			name = CheckForExistingAssociationName(name, existingAssociations, ref isDupe);
			
			return name;
		}

		private static string CheckForExistingAssociationName(string name, List<Association> existingAssociations, ref bool isDupe) {
			string newName = name;
			
			// Look for name in existing associations
			Association existingAssociation = existingAssociations.FirstOrDefault(a => a.Name == name);
			if (existingAssociation != null) {
				isDupe = true;
				// See if last character is a number
				Match match = Regex.Match(name.Substring(name.Length-1), @"\d");

				if (!string.IsNullOrEmpty(match.Value)) {
					newName = name.Substring(0, name.Length - 1) + (Convert.ToInt32(match.Value) + 1).ToString();
					// There may already be another association with the same name + 1, so keep going
					newName = CheckForExistingAssociationName(newName, existingAssociations, ref isDupe);
				}
				else {
					newName = name + "1";
				}
			}

			return newName;
		}

		public static string BuildDbmlTableName(string schema, string tableName) {
			return WrapSqlReservedWords(schema) + "." + WrapSqlReservedWords(tableName);
		}

		public static string GetCSharpVariableType(string dbmlType, bool canBeNull) {
			string csharpType = "UNKNOWN";
			
			switch (dbmlType) {
				case "System.String": csharpType = "string"; break;
				case "object": csharpType = "object"; break;
				case "System.Byte[]": csharpType = "System.Byte[]"; break;
				case "System.Data.Linq.Binary": csharpType = "System.Data.Linq.Binary"; break;
				case "System.Xml.Linq.XElement": csharpType = "System.Xml.Linq.XElement"; break;
				case "System.Char": csharpType = (canBeNull) ? "System.Nullable<char>" : "char"; break;
				case "System.Int16": csharpType = (canBeNull) ? "System.Nullable<short>" : "short"; break;
				case "System.Int32": csharpType = (canBeNull) ? "System.Nullable<int>" : "int"; break;
				case "System.Int64": csharpType = (canBeNull) ? "System.Nullable<long>" : "long"; break;
				case "System.DateTime": csharpType = (canBeNull) ? "System.Nullable<DateTime>" : "DateTime"; break;
				case "System.Boolean": csharpType = (canBeNull) ? "System.Nullable<bool>" : "bool"; break;
				case "System.Byte": csharpType = (canBeNull) ? "System.Nullable<byte>" : "byte"; break;
				case "System.Guid": csharpType = (canBeNull) ? "System.Nullable<Guid>" : "Guid"; break;
				case "System.Double": csharpType = (canBeNull) ? "System.Nullable<double>" : "double"; break;
				case "System.Decimal": csharpType = (canBeNull) ? "System.Nullable<decimal>" : "decimal"; break;
				//case "System.Data.Linq.Binary": csharpType = "System.Data.Linq.Binary"; break;
				//case "System.Xml.Linq.XElement": csharpType = "System.Xml.Linq.XElement"; break;
				//case "System.Char": csharpType = "System.Nullable<char>"; break;
				//case "System.Int16": csharpType = "System.Nullable<short>"; break;
				//case "System.Int32": csharpType = "System.Nullable<int>"; break;
				//case "System.Int64": csharpType = "System.Nullable<long>"; break;
				//case "System.DateTime": csharpType = "System.Nullable<DateTime>"; break;
				//case "System.Boolean": csharpType = "System.Nullable<bool>"; break;
				//case "System.Byte": csharpType = "System.Nullable<byte>"; break;
				//case "System.Guid": csharpType = "System.Nullable<Guid>"; break;
				//case "System.Double": csharpType = "System.Nullable<double>"; break;
				//case "System.Decimal": csharpType = "System.Nullable<decimal>"; break;
				default: break;
			}

			return csharpType;
		}

		public static string GetCSharpVariableName(dotNails.CodeGen.Models.Column column) {
			string variableName;

			if (!string.IsNullOrEmpty(column.Member)) {
				variableName = column.Member;
			}
			else {
				variableName = column.Name;
			}

			return variableName;
		}

		public static string GetDbmlTypeFromDbType(string dbType, string maxLength)
		{
			string dbmlType = "UNDEFINED";
			dbType = dbType.ToLower();
			
			switch (dbType) {
				case "nvarchar":
				case "varchar":
				case "varcharmax": dbmlType = "System.String"; break;
				case "bigint": dbmlType = "System.Int64"; break;
				case "binary":
				case "varbinary":
				case "varbinarymax": dbmlType = "System.Data.Linq.Binary"; break;
				case "bit": dbmlType = "System.Boolean"; break;
				case "datetime": dbmlType = "System.DateTime"; break;
				case "smallint": dbmlType = "System.Int16"; break;
				case "int": dbmlType = "System.Int32"; break;
				case "tinyint": dbmlType = "System.Byte"; break;
				case "uniqueidentifier": dbmlType = "System.Guid"; break;
				case "decimal": dbmlType = "System.Decimal"; break;
				case "money": dbmlType = "System.Decimal"; break;
				case "numeric": dbmlType = "System.Decimal"; break;
				case "float": dbmlType = "System.Double"; break;
				case "timestamp": dbmlType = "System.Data.Linq.Binary"; break;
				case "text": dbmlType = "System.String"; break;
				case "image": dbmlType = "System.Data.Linq.Binary"; break;
				case "xml": dbmlType = "System.Xml.Linq.XElement"; break;
				default:
					if (dbType.StartsWith("char") ||
						dbType.StartsWith("nchar")) {
						int length = int.Parse(maxLength);
						if (length > 1) {
							dbmlType = "System.String";
						}
						else {
							dbmlType = "System.Char";
						}
					}
					break;
			}

			return dbmlType;

				//case "bigint": return "BigInt";
				//case "binary": return "Binary";
				//case "bit": return "Bit";
				//case "char": return "Char";
				//case "datetime": return "DateTime";
				//case "decimal": return "Decimal";
				//case "float": return "Float";
				//case "image": return "Image";
				//case "int": return "Int";
				//case "money": return "Money";
				//case "nchar": return "NChar";
				//case "ntext": return "NText";
				//case "numeric": return "Decimal";
				//case "nvarchar": return "NVarChar";
				//case "real": return "Real";
				//case "smalldatetime": return "SmallDateTime";
				//case "smallint": return "SmallInt";
				//case "smallmoney": return "SmallMoney";
				//case "sql_variant": return "Variant";
				//case "sysname": return "NChar";
				//case "text": return "Text";
				//case "timestamp": return "Timestamp";
				//case "tinyint": return "TinyInt";
				//case "uniqueidentifier": return "UniqueIdentifier";
				//case "varbinary": return "VarBinary";
				//case "varchar": return "VarChar";
				//default: return "__UNKNOWN__" + column.NativeType;
		    
		}
		
		/// <summary>
		/// Expects a DataRow that is column MetaData from a SqlDataReader.GetSchema() call
		/// </summary>
		/// <param name="column"></param>
		/// <returns></returns>
		public static string GetDbmlDbType(DataRow column) {
			return GetDbmlDbType(column["DataTypeName"].ToString(), column["ColumnSize"].ToString(), Convert.ToBoolean(column["AllowDBNull"]), false, Convert.ToInt32(column["NumericPrecision"]), Convert.ToInt32(column["NumericScale"]));
		}

		public static string GetDbmlDbType(DataType dataType, bool nullable, bool identity) {
			return GetDbmlDbType(dataType.SqlDataType.ToString(), dataType.MaximumLength.ToString(), nullable, identity, dataType.NumericPrecision, dataType.NumericScale);
		}

		public static string GetDbmlDbType(string sqlDataType, string maxLength, bool nullable, bool identity, int numericPrecision, int numericScale) {
			StringBuilder dbType = new StringBuilder();
			dbType.Append(sqlDataType);

			switch (dbType.ToString().ToLower()) {
				case "nvarchar":
				case "varchar":
				case "char":
				case "nchar":
					dbType.Append("(").Append(maxLength).Append(")");
					break;
				case "varcharmax":
					dbType.Length = 0;
					dbType.Append("VarChar(MAX)");
					break;
				case "nvarcharmax":
					dbType.Length = 0;
					dbType.Append("NVarChar(MAX)");
					break;
				case "binary":
				case "varbinary":
					dbType.Append("(").Append(maxLength).Append(")");
					break;
				case "varbinarymax":
					dbType.Length = 0;
					dbType.Append("VarBinary(MAX)");
					break;
				case "float":
					dbType.Length = 0;
					dbType.Append("Float");
					break;
				case "numeric":
				case "decimal":
					dbType.Length = 0;
					dbType.Append(string.Format("Decimal({0},{1})", numericPrecision.ToString(), numericScale.ToString()));
					break;
				case "timestamp":
					dbType.Length = 0;
					dbType.Append("rowversion");
					break;
			}

			if (!nullable) {
				dbType.Append(" NOT NULL");
			}
			if (identity) {
				dbType.Append(" IDENTITY");
			}

			return dbType.ToString();
		}

		public static string WrapSqlReservedWords(string word) {
			if (SQL_RESERVED_WORDS.Contains(word.ToLower())) {
				word = "[" + word + "]";
			}

			return word;
		}

		public static void SetReferencedRelationshipFromReferencingRelationship(TableRelationship newRelationship, TableRelationship relationship, string tableName) {
			newRelationship.IsReferencingTable = false;
			newRelationship.ReferencedTable = relationship.ReferencedTable;
			newRelationship.ReferencedTableSchema = relationship.ReferencedTableSchema;
			newRelationship.ReferencedColumn = relationship.ReferencedColumn;
			newRelationship.ReferencingTable = relationship.ReferencingTable;
			//newRelationship.ReferencingTableSchema = relationship.ReferencingTableSchema; // don't have this yet from referencing table
			newRelationship.ReferencingColumn = relationship.ReferencingColumn;
			newRelationship.ForeignKeyName = relationship.ForeignKeyName;
			//newRelationship.TableName = tableName;  // deprecate
			newRelationship.Paired = false;

		}

		public static void SetReferencingRelationshipFromReferencedRelationship(TableRelationship newRelationship, TableRelationship relationship, string tableName) {
			newRelationship.IsReferencingTable = true;
			newRelationship.ReferencedTable = relationship.ReferencedTable;
			//newRelationship.ReferencedTableSchema = relationship.ReferencedTableSchema;	// don't have these yet from referenced table
			//newRelationship.ReferencedColumn = relationship.ReferencedColumn;				// don't have these yet from referenced table
			newRelationship.ReferencingTable = relationship.ReferencingTable;
			newRelationship.ReferencingTableSchema = relationship.ReferencingTableSchema;
			//newRelationship.ReferencingColumn = relationship.ReferencingColumn;			// don't have these yet from referenced table
			newRelationship.ForeignKeyName = relationship.ForeignKeyName;
			//newRelationship.TableName = tableName;
			newRelationship.Paired = false;
		}

		public static void PairTableRelationships(List<dotNails.CodeGen.Models.Table> dbmlTables) {
			// Find all the pairings of referencing and referenced keys.  Any that don't pair up mean one of the tables in the relationship
			//  is not present in our dbml.
			// TODO: This currently only works on the tables being added at this time.  Correct to take into account the tables that were 
			//  already in the dbml. ??
			List<TableRelationship> referencingRelationships = new List<TableRelationship>();
			List<TableRelationship> referencedRelationships = new List<TableRelationship>();

			// Separate the Referencing relationships in all tables from the referenced relationships
			foreach (dotNails.CodeGen.Models.Table dbmlTable in dbmlTables) {
				foreach (TableRelationship relationship in dbmlTable.TableRelationships) {
					// Only look at non-paired relationships
					if (!relationship.Paired) {
						if (relationship.IsReferencingTable) {
							referencingRelationships.Add(relationship);
						}
						else {
							referencedRelationships.Add(relationship);
						}
					}
				}
			}

			// Try to pair the referencing relationships
			foreach (TableRelationship referencingRelationship in referencingRelationships) {
				TableRelationship referencedRelationship = referencedRelationships.Find(r => !r.Paired && r.ForeignKeyName == referencingRelationship.ForeignKeyName);
				//TableRelationship referencedRelationship = (from r in referencedRelationships
				//                                            where r.ForeignKeyName == referencingRelationships[i].ForeignKeyName
				//                                            select r).SingleOrDefault();

				if (referencedRelationship != null) {
					referencedRelationship.Paired = true;
					referencingRelationship.Paired = true;

					// Flesh out all properties of the referenced column, so it has everything needed to create an association
					referencedRelationship.ReferencedColumn = referencingRelationship.ReferencedColumn;
					referencedRelationship.ReferencingColumn = referencingRelationship.ReferencingColumn;

					// Flesh out all properties of the referencing column, so it has everything needed to create an association
					referencingRelationship.ReferencingTableSchema = referencedRelationship.ReferencingTableSchema;
				}
			}
		}

		public static void PopulateAssociations(List<dotNails.CodeGen.Models.Table> dbmlTables) {
			// Create the Associations
			foreach (dotNails.CodeGen.Models.Table dbmlTable in dbmlTables) {
				foreach (TableRelationship relationship in dbmlTable.TableRelationships) {
					if (relationship.Paired) {
						// Name ReferencedTable.Type.Name + "_" + ReferencingTable.Type.Name
						bool isDupe = false;
						string associationName = DbmlUtil.CreateAssociationName(relationship, dbmlTable.Type.Associations, ref isDupe);
						string member = string.Empty;
						string thisKey = string.Empty;
						string otherKey = string.Empty;
						string type = string.Empty;

						if (relationship.IsReferencingTable) {
							// Get the column in this table
							dotNails.CodeGen.Models.Column thisColumn = dbmlTable.Type.Columns.Find(c => c.Name == relationship.ReferencingColumn);
							if (thisColumn != null) {
								// ThisKey = the Table.Type.Column.Member of the column in this table
								bool hasMemberProperty = HasMemberProperty(thisColumn); // will set the Member property if underscores are found in column.Name

								thisKey = hasMemberProperty ? thisColumn.Member : thisColumn.Name;
							}
							
							// Get the other table
							string otherTableName = DbmlUtil.BuildDbmlTableName(relationship.ReferencedTableSchema, relationship.ReferencedTable);
							dotNails.CodeGen.Models.Table otherTable = dbmlTables.Find(t => t.Name == otherTableName);
							//dotNails.CodeGen.Models.Table otherTable = dbmlTables.Find(t => t.Type.Name == relationship.ReferencedTable);
							//dotNails.CodeGen.Models.Table otherTable = dbmlTables.Find(t => t.Name == GetDbmlTableNameFromTableName(relationship.ReferencedTableSchema, relationship.ReferencedTable));
							if (otherTable != null) {
								if (isDupe) {
									// There was another association with the exact same name, so there's already a member with the same name.  For this 
									//  member name, instead of just otherTable.Type.Name, we'll use thisKey + otherTable.Type.Name
									member = thisKey + otherTable.Type.Name;
								}
								else {
									// Member = the ReferencedTable.Type.Name for the referencing table
									member = otherTable.Type.Name;
								}
								// Type = the Table.Type.Name of the other table
								type = otherTable.Type.Name;
								dotNails.CodeGen.Models.Column otherColumn = otherTable.Type.Columns.Find(c => c.Name == relationship.ReferencedColumn);
								if (otherColumn != null) {
									// OtherKey = the Table.Type.Column.Member of the column in the other table
									bool hasMemberProperty = HasMemberProperty(otherColumn); // will set the Member property if underscores are found in column.Name
									otherKey = hasMemberProperty ? otherColumn.Member : otherColumn.Name;
								}
							}
						}
						else {
							// Get the other table
							string otherTableName = DbmlUtil.BuildDbmlTableName(relationship.ReferencingTableSchema, relationship.ReferencingTable);
							dotNails.CodeGen.Models.Table otherTable = dbmlTables.Find(t => t.Name == otherTableName); 
							//dotNails.CodeGen.Models.Table otherTable = dbmlTables.Find(t => t.Type.Name == relationship.ReferencingTable);
							//dotNails.CodeGen.Models.Table otherTable = dbmlTables.Find(t => t.Name == GetDbmlTableNameFromTableName(relationship.ReferencingTableSchema, relationship.ReferencingTable));
							if (otherTable != null) {
								// Type = the Table.Type.Name of the other table
								type = otherTable.Type.Name;
								bool otherTableHasMemberProperty = HasMemberProperty(otherTable); // will set the Member property if underscores are found in column.Name
									
								dotNails.CodeGen.Models.Column otherColumn = otherTable.Type.Columns.Find(c => c.Name == relationship.ReferencingColumn);
									
								if (otherColumn != null) {
									// OtherKey = the Table.Type.Column.Member of the column in the other table
									bool hasMemberProperty = HasMemberProperty(otherColumn); // will set the Member property if underscores are found in column.Name
									otherKey = hasMemberProperty ? otherColumn.Member : otherColumn.Name;
								}

								if (isDupe) {
									// There was another association with the exact same name, so there's already a member with the same name.  For this 
									//  member name, instead of just otherTable.Member, we'll use otherKey + otherTable.Member
									member = otherKey;
									member += otherTableHasMemberProperty ? otherTable.Member : otherTable.Name;
								}
								else {
									// Member = the ReferencingTable.Member for the referenced table
									member = otherTableHasMemberProperty ? otherTable.Member : otherTable.Name;
								}
							}

							// Get the column in this table
							dotNails.CodeGen.Models.Column thisColumn = dbmlTable.Type.Columns.Find(c => c.Name == relationship.ReferencedColumn);
							if (thisColumn != null) {
								// ThisKey = the Table.Type.Column.Member of the column in this table
								bool hasMemberProperty = HasMemberProperty(thisColumn); // will set the Member property if underscores are found in column.Name
								
								thisKey = hasMemberProperty ? thisColumn.Member : thisColumn.Name;
							}
						}

						Association association = new Association {
							Name = associationName,
							IsForeignKey = relationship.IsReferencingTable,
							Member = member,
							ThisKey = thisKey,
							OtherKey = otherKey,
							Type = type
						};

						dbmlTable.Type.Associations.Add(association);
					}
				}
			}

		}

		/// <summary>
		/// Returns whether or not the Member property is set, and sets the Member property to Pascal case if underscores are found in column.Name
		/// </summary>
		/// <param name="column"></param>
		/// <returns>Returns true if Member property has a value, returns false otherwise (will only return false if Name has no underscores)</returns>
		public static bool HasMemberProperty(dotNails.CodeGen.Models.Column column) {
			bool result = false;

			if (string.IsNullOrEmpty(column.Member)) {
				string pascalCaseName = StringUtil.GetPascalCaseName(column.Name);  // this will only change the string if underscores are present
				if (pascalCaseName != column.Name) {
					column.Member = pascalCaseName;
					result = true;
				}
			}
			else {
				// The Member property is already populated - do nothing, and return that we have a Member Property.
				result = true;
			}

			return result;
		}

		/// <summary>
		/// Returns whether or not the Member property is set, and sets the Member property to Pascal case if underscores are found in column.Name
		/// </summary>
		/// <param name="column"></param>
		/// <returns>Returns true if Member property has a value, returns false otherwise (will only return false if Name has no underscores)</returns>
		public static bool HasMemberProperty(dotNails.CodeGen.Models.Table table) {
			bool result = false;

			if (string.IsNullOrEmpty(table.Member)) {
				string pascalCaseName = StringUtil.GetPascalCaseName(table.Name);  // this will only change the string if underscores are present
				if (pascalCaseName != table.Name) {
					table.Member = pascalCaseName;
					result = true;
				}
			}
			else {
				// The Member property is already populated - do nothing, and return that we have a Member Property.
				result = true;
			}

			return result;
		}

		public static void MergeNewDbmlWithOld(DbmlData newDbml, DbmlData oldDbml) {
			// Search for all the new table names in the old dbml
			foreach (dotNails.CodeGen.Models.Table newDbmlTable in newDbml.DB.Tables) {
				dotNails.CodeGen.Models.Table existingTable = oldDbml.DB.Tables.Find(t => t.Name == newDbmlTable.Name);
				if (existingTable != null) {
					// Remove the old table because it will be recreated with the new dbml data
					oldDbml.DB.Tables.Remove(existingTable);
				}
			}

			// Add all the new tables to the old dbml
			oldDbml.DB.Tables.AddRange(newDbml.DB.Tables);

			// Search for all the new function names in the old dbml
			foreach (dotNails.CodeGen.Models.Function newDbmlFunction in newDbml.DB.Functions) {
				dotNails.CodeGen.Models.Function existingFunction = oldDbml.DB.Functions.Find(f => f.Name == newDbmlFunction.Name);
				if (existingFunction != null) {
					// Remove the old function because it will be recreated with the new dbml data
					oldDbml.DB.Functions.Remove(existingFunction);
				}
			}

			// Add all the new functions to the old dbml
			oldDbml.DB.Functions.AddRange(newDbml.DB.Functions);

			oldDbml.InitializeDerivedProperties();
		}

		#region Constants
		// Finish this list, then keep it up to date - http://developer.mimer.se/validator/sql-reserved-words.tml
		static List<string> SQL_RESERVED_WORDS = new List<string>() {
													"add",
													"all",
													"allocate",
													"alter",
													"and",
													"any",
													"are",
													"array",
													"as",
													"asensitive",
													"asymmetric",
													"at",
													"atomic",
													"authorization",
													"begin",
													"group"
												};
		#endregion Constants

		/// <summary>
		/// Produces valid values.  Nothing fancy, just valid values for sql types.
		/// </summary>
		/// <param name="systemType"></param>
		/// <returns></returns>
		public static string GetValidTypeValueForDB(string systemType) {
			string value = string.Empty;

			switch(systemType.ToLower()) {
				case "system.int64": value = "1"; break;
				case "system.data.linq.binary": value = "null"; break;
				case "system.boolean": value = "1"; break;
				case "system.char": value = "'a'"; break;
				case "system.datetime": value = "'1/1/1980'"; break;
				case "system.int16": value = "1"; break;
				case "system.int32": value = "1"; break;
				case "system.byte": value = "1"; break;
				case "system.string": value = "'t'"; break;
				case "system.guid": value = "38CD15C0-66F1-4aa7-92A5-837093516A44"; break;
				default: value = "1"; break;
			}

			return value;
		}

		public static string GetParameterDirection(bool isOutputParameter) {
			string direction = string.Empty;

			if (isOutputParameter) {
				direction = "InOut";
			}

			return direction;
		}
	}
}
